library(readxl)
library(dplyr)
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
library(ggplot2)
library(plotly)
## 
## Attaching package: 'plotly'
## The following object is masked from 'package:ggplot2':
## 
##     last_plot
## The following object is masked from 'package:stats':
## 
##     filter
## The following object is masked from 'package:graphics':
## 
##     layout
library(RColorBrewer)
data = read_xls('RetinoicAcid_Validation_20220825_clean.xls',
                col_types = c('numeric', 'skip', 'skip', 'text', 'guess',
                              'numeric', 'numeric', 'skip', 'numeric'))
data.1 = data %>%
    cbind('stock' = c(rep(1, 9), NA, rep(2, 9), NA, rep(3, 9), NA,
                      rep(c(4, 5, 6), 4), NA, 4, 5, 6)) %>%
    cbind('type' = 
          c(rep(
              c(rep('Cal', 9), 'blank'), 3),
            rep('QC', 12),
            'blank',
            rep('sample', 3))
    ) %>%
    cbind('concentration' = c(
        rep(
            c(500, 200, 100, 50, 20, 10, 5, 2, 1, 0), 3
        ),
        rep(500, 3), rep(20, 3), rep(3, 3), rep(1, 3),
        0, rep(100, 3)))

data.group = data.1 %>% group_by(stock)

data.group.cal = data.group %>%
    filter(type == 'Cal')
cal.curve = lm(Area ~ concentration, data = data.group.cal, weights = 1/concentration)
summary(cal.curve)
## 
## Call:
## lm(formula = Area ~ concentration, data = data.group.cal, weights = 1/concentration)
## 
## Weighted Residuals:
##     Min      1Q  Median      3Q     Max 
## -7.3468 -2.3563 -0.1021  0.7839 11.2085 
## 
## Coefficients:
##               Estimate Std. Error t value Pr(>|t|)    
## (Intercept)   -4.54937    1.61300   -2.82  0.00925 ** 
## concentration 14.35951    0.07436  193.12  < 2e-16 ***
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 3.744 on 25 degrees of freedom
## Multiple R-squared:  0.9993, Adjusted R-squared:  0.9993 
## F-statistic: 3.73e+04 on 1 and 25 DF,  p-value: < 2.2e-16
cal.plot = ggplot(data.group, mapping = aes(concentration, Area)) +
    geom_point(aes(colour = stock, shape = type)) +
#    stat
    geom_smooth(data = data.group.cal, 
        method = 'lm', formula = y ~ x) +
    theme_classic()
#cal.plot
#
#cal.plotly = plot_ly(data = data.group, x = ~concentration, y = ~Area,
#                     color = ~stock, colors = "Reds", symbol = ~type)
#cal.plotly

ggplotly(cal.plot)
data.post = data.group %>%
    mutate(calc.conc = (Area - cal.curve$coefficients[1])/
               cal.curve$coefficients[2],
           accuracy = calc.conc/concentration)
data.post